﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Text;
using TraditionalMalteseCuisine.App_Code.Functions;

namespace TraditionalMalteseCuisine.App_Code.Objects
{
    [Serializable]
    public class SiteMember : BaseTableWithMediaFields
    {
        //*************************************************
        //                Class Members
        //*************************************************
        #region Class Members
        public const string DEFAULT_IMAGE_LOCATION = DEFAULT_IMAGE_FOLDER_LOCATION + "default_profile.gif";
        public const string DEFAULT_THUMBNAIL_LOCATION = DEFAULT_THUMBNAIL_FOLDER_LOCATION + "default_profile.gif";

        public const string TABLE_NAME = "SiteMembers";
        public const string VIEW_NAME_SITE_MEMBERS_TOTAL_RECIPES_POSTED = "vw" + TABLE_NAME + "TotalRecipesPosted";
        public const string VIEW_NAME_SITE_MEMBERS_TOTAL_RECIPES_COMMENTS_POSTED = "vw" + TABLE_NAME + "TotalRecipesCommentsPosted";
        
        public const string USERNAME_TAG = "username";
        public const string PASSWORD_TAG = "password";
        public const string EMAIL_TAG = "email";
        public const string FIRSTNAME_TAG = "firstname";
        public const string LASTNAME_TAG = "lastname";
        public const string DOB_TAG = "date_of_birth";
        public const string GENDER_TAG = "gender";
        public const string REGISTRATION_DATE_TAG = "registration_date";
        public const string LAST_LOGIN_TAG = "last_login";

        public const string AVATAR_TAG = "avatar";
        public const string AVATAR_IMAGE_TAG = "avatar_image";
        public const string TOTAL_RECIPES_POSTED_TAG = "TotalRecipesPosted";
        public const string TOTAL_RECIPES_COMMENTS_POSTED_TAG = "TotalRecipesCommentsPosted";

        private string mUsername = string.Empty;
        private string mPassword = string.Empty;
        private string mEMail = string.Empty;
        private string mFirstName = string.Empty;
        private string mLastName = string.Empty;
        private string mGender = string.Empty;
        private DateTime mDOB = DateTime.MinValue;
        private DateTime mRegistrationDate = DateTime.MinValue;
        private string mLastLogin = "This is your first time logging in!";
        #endregion
        //*************************************************


        //*************************************************
        //                 Constructor
        //*************************************************
        #region Constructor
        public SiteMember()
        {
            ImageLocation = DEFAULT_IMAGE_LOCATION;
            ThumbnailLocation = DEFAULT_THUMBNAIL_LOCATION;
        }
        #endregion
        //*************************************************


        //*************************************************
        //               Static Methods
        //*************************************************
        #region Static Methods
        public static OleDbCommand GetCommand_GetTopContributorsInRecipesPosted(int amount)
        {
            OleDbCommand oledbCommand = new OleDbCommand();
            oledbCommand.CommandType = CommandType.Text;

            StringBuilder commandText = new StringBuilder();
            commandText.AppendFormat("SELECT TOP {0} * FROM {1} ORDER BY {2} DESC, {3} ASC, {4} ASC", amount, VIEW_NAME_SITE_MEMBERS_TOTAL_RECIPES_POSTED, TOTAL_RECIPES_POSTED_TAG, FIRSTNAME_TAG, LASTNAME_TAG);

            oledbCommand.CommandText = commandText.ToString();

            return oledbCommand;
        }

        public static OleDbCommand GetCommand_GetTopContributorsInRecipesCommentsPosted(int amount)
        {
            OleDbCommand oledbCommand = new OleDbCommand();
            oledbCommand.CommandType = CommandType.Text;

            StringBuilder commandText = new StringBuilder();
            commandText.AppendFormat("SELECT TOP {0} * FROM {1} ORDER BY {2} DESC, {3} ASC, {4} ASC", amount, VIEW_NAME_SITE_MEMBERS_TOTAL_RECIPES_COMMENTS_POSTED, TOTAL_RECIPES_COMMENTS_POSTED_TAG, FIRSTNAME_TAG, LASTNAME_TAG);

            oledbCommand.CommandText = commandText.ToString();

            return oledbCommand;
        }
        #endregion
        //*************************************************


        //*************************************************
        //                   Methods
        //*************************************************
        #region Methods
        protected override string GetInsertScriptUsingParameters()
        {
            StringBuilder insertScript = new StringBuilder();
            
            insertScript.Append("INSERT INTO ");
            insertScript.AppendFormat("{0} ", TABLE_NAME);
            insertScript.AppendFormat("([{0}], [{1}], [{2}], [{3}], [{4}], [{5}], [{6}], [{7}], [{8}]) ", USERNAME_TAG, PASSWORD_TAG, THUMBNAIL_LOCATION_TAG, IMAGE_LOCATION_TAG, FIRSTNAME_TAG, LASTNAME_TAG, DOB_TAG, GENDER_TAG, EMAIL_TAG, REGISTRATION_DATE_TAG);
            insertScript.Append("VALUES ");
            insertScript.AppendFormat("(@{0}, @{1}, @{2}, @{3}, @{4}, @{5}, @{6}, @{7}, @{8}) ", USERNAME_TAG, PASSWORD_TAG, THUMBNAIL_LOCATION_TAG, IMAGE_LOCATION_TAG, FIRSTNAME_TAG, LASTNAME_TAG, DOB_TAG, GENDER_TAG, EMAIL_TAG, REGISTRATION_DATE_TAG);

            return insertScript.ToString();
        }

        public override OleDbCommand GetInsertCommand()
        {
            OleDbCommand insertCommand = new OleDbCommand();

            insertCommand.CommandType = CommandType.Text;
            insertCommand.CommandText = GetInsertScriptUsingParameters();

            insertCommand.Parameters.AddWithValue(string.Format("@{0}", USERNAME_TAG), Username);
            insertCommand.Parameters.AddWithValue(string.Format("@{0}", PASSWORD_TAG), EncryptPassword);
            insertCommand.Parameters.AddWithValue(string.Format("@{0}", THUMBNAIL_LOCATION_TAG), ThumbnailLocation);
            insertCommand.Parameters.AddWithValue(string.Format("@{0}", IMAGE_LOCATION_TAG), ImageLocation);
            insertCommand.Parameters.AddWithValue(string.Format("@{0}", FIRSTNAME_TAG), FirstName);
            insertCommand.Parameters.AddWithValue(string.Format("@{0}", LASTNAME_TAG), LastName);
            insertCommand.Parameters.AddWithValue(string.Format("@{0}", DOB_TAG), DateTime_Functions.GetShortDate(DOB));
            insertCommand.Parameters.AddWithValue(string.Format("@{0}", GENDER_TAG), Gender);
            insertCommand.Parameters.AddWithValue(string.Format("@{0}", EMAIL_TAG), EMail);
            insertCommand.Parameters.AddWithValue(string.Format("@{0}", REGISTRATION_DATE_TAG), DateTime_Functions.GetShortDateTime((RegistrationDate = DateTime.Now)));

            return insertCommand;
        }



        public override BaseTable ReadDataRow(DataRow datarow)
        {
            ID = int.Parse(datarow[ID_TAG].ToString());
            Username = datarow[USERNAME_TAG].ToString();
            Password = datarow[PASSWORD_TAG].ToString();
            Gender = datarow[GENDER_TAG].ToString();
            EMail = datarow[EMAIL_TAG].ToString();
            ThumbnailLocation = datarow[THUMBNAIL_LOCATION_TAG].ToString();
            ImageLocation = datarow[IMAGE_LOCATION_TAG].ToString();
            FirstName = datarow[FIRSTNAME_TAG].ToString();
            LastName = datarow[LASTNAME_TAG].ToString();
            DOB = DateTime.Parse(datarow[DOB_TAG].ToString());
            RegistrationDate = DateTime.Parse(datarow[REGISTRATION_DATE_TAG].ToString());
            InUse = bool.Parse(datarow[IN_USE_TAG].ToString());

            return this;
        }


        public override string ToString()
        {
            return string.Format("{0} {1}", FirstName, LastName);
        }
        #endregion
        //*************************************************


        //*************************************************
        //                  Properties
        //*************************************************
        #region Properties
        public string Username
        {
            get { return mUsername; }
            set { mUsername = value; }
        }

        public string Password
        {
            get { return mPassword; }
            set { mPassword = value; }
        }

        public string EncryptPassword
        {
            get { return Hash_Functions.HashPassword(Password); }
        }

        public string EMail
        {
            get { return mEMail; }
            set { mEMail = value; }
        }

        public string FirstName
        {
            get { return mFirstName; }
            set { mFirstName = value; }
        }

        public string LastName
        {
            get { return mLastName; }
            set { mLastName = value; }
        }

        public DateTime DOB
        {
            get { return mDOB; }
            set { mDOB = value; }
        }

        public string Gender
        {
            get { return mGender; }
            set { mGender = value; }
        }

        public DateTime RegistrationDate
        {
            get { return mRegistrationDate; }
            set { mRegistrationDate = value; }
        }

        public string LastLogin
        {
            get { return mLastLogin; }
            set { mLastLogin = value; }
        }
        #endregion
        //*************************************************
    }
}